﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Collections;

namespace ViewRidgeAssistant
{

    public class data_source
    {
        private string _conn_status;                     //Состояние коннекта
        private SqlConnection _conn;                     //Подключение к базе данных vrdb
        private DataSet _ds;                             //Набор данных (таблиц)
        private SqlDataAdapter _da;                      //Адаптер
        private string _connection_string;               //Параметры подключения к базе данных
        private string _command_text;                    //Команда адаптера
        private SqlCommandBuilder _cb;                   //Автоматизация построения запросов адаптера
        private SqlDataAdapter _da_customer;             //Адаптер таблицы CUSTOMER
        private string _active_table;                    //последняя запрошенная таблица/адаптер

        /// <summary>
        /// Пустой конструктор
        /// </summary>
        public data_source()
        {
            this._conn = new SqlConnection();
            this._ds = new DataSet();
            this._conn_status = "DISCONNECTED";
        }

        /// <summary>
        /// Получение статуса соединения с базой данных
        /// </summary>
        /// <returns></returns>
        public string get_connect_status()
        {
            return this._conn_status;
        }

        /// <summary>
        /// Установка параметров подключения к базе данных
        /// </summary>
        /// <param name="conn_string">Строка подключения</param>
        public bool set_connection_settings(string conn_string)
        {
            this._connection_string = conn_string;
            try
            {
                this._conn.ConnectionString = this._connection_string;
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// Установка соединения с базой данных
        /// </summary>
        /// <returns></returns>
        public string connect_to_db()
        {
            this._conn_status = "PROCESS_CONNECTING";
            //this._conn.ConnectionString = this._connection_string;
            try
            {
                this._conn.Open();
            }
            catch (Exception ex)
            {
                //Записать в лог подробности ошибки коннекта
                this._conn_status = "DISCONNECTED";
                return ex.Data + " " + ex.Message;
            }
            this._conn_status = "CONNECTED";
            return "OK";
        }

        /// <summary>
        /// Разорвать соединение с базой данных
        /// </summary>
        /// <returns></returns>
        public string disconnect_db()
        {
            if (this._conn_status == "CONNECTED")
            {
                this._conn_status = "PROCESS_DISCONNECTING";
                try
                {
                    this._conn.Close();
                }
                catch (Exception ex)
                {
                    //Записать в лог подробности ошибки дисконнекта
                    this._conn_status = "CONNECTED";
                    return ex.Data + " " + ex.Message; ;
                }
                this._conn_status = "DISCONNECTED";
                return "OK";
            }
            else
                if (this._conn_status == "DISCONNECTED") 
                { 
                    return "OK";
                }
                else
                    if (this._conn_status == "PROCESS_DISCONNECTING")
                    {
                        return "MODULE_IN_PROCESS_DISCONNECTING";
                    }
                    else
                        if (this._conn_status == "PROCESS_CONNECTING")
                        {
                            return "MODULE_IN_PROCESS_CONNECTING";
                        }
                        else return "UNKNOWN_ERROR";
        }

        /// <summary>
        /// Возвращает таблицу в зависимости от заданного имени
        /// </summary>
        /// <param name="table_name">Имя таблицы</param>
        /// <returns>DataTable</returns>
        public DataSet get_data_table(string table_name)
        {
            string query = "";
            query += "select";
            query += " *";
            query += " from ";
            query += table_name;
            try
            {
                this._ds = new DataSet();
                this._da = new SqlDataAdapter(query, this._conn);
                this._cb = new SqlCommandBuilder(this._da);

                this._da.Fill(this._ds, table_name);
                return this._ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Data + " " + ex.Message);
                return null;
            }
        }

        public DataTable get_works_by_param(bool b_title, bool b_copy, bool b_artist, bool b_desc,
                                        string s_title, string s_copy, string s_artist, string s_desc)
        {
            DataTable table = new DataTable();
            string query = "";
            query += "select WORK.WorkID, WORK.Title, WORK.Copy, WORK.Description, ARTIST.Name as ArtistName from WORK inner join ARTIST on WORK.ArtistID = ARTIST.ArtistID";
            if ((b_title) || (b_copy) || (b_artist) || (b_desc))
            {
                query += " where ";
                query += b_title ? "WORK.Title like '%" + s_title + "%'" : " 1=1 ";
                query += " and ";
                query += b_copy ? "WORK.Copy like '%" + s_copy + "%'" : " 1=1 ";
                query += " and "; //denis.ivanov@modelware.ru
                query += b_artist ? "ARTIST.Name like '%" + s_artist + "%'" : " 1=1 ";
                query += " and ";
                query += b_desc ? "WORK.Description like '%" + s_desc + "%'" : " 1=1 ";
            }
            //MessageBox.Show(query);
            try
            {
                this._ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(query, this._conn);
                try { this._ds.Tables["FIND_WORKS"].Clear(); }
                catch (Exception ex) { };
                da.Fill(this._ds, "FIND_WORKS");
                table = this._ds.Tables["FIND_WORKS"];
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                //Разобраться почему ошибка
                return null;
            }
            return table;
        }


        public DataTable get_artists_by_param(bool b_name, bool b_nat, bool b_bd_date, bool b_dd_date,
                                                string name, string nat, int start_bd_date, int end_bd_date,
                                                                         int start_dd_date, int end_dd_date)
        {
            DataTable table = new DataTable();
            string query = "";
            query += "select ArtistID, Name, BirthYear, DeceaseYear, Nationality from ARTIST";
            if ((b_name) || (b_nat) || (b_bd_date) || (b_dd_date))
            {
                query += " where ";
                query += b_name ? "Name like '%" + name + "%'" : " 1=1 ";
                query += " and ";
                query += b_nat ? "Nationality like '%" + nat + "%'" : " 1=1 ";
                query += " and ";
                query += b_bd_date ? "BirthYear>=" + start_bd_date.ToString() + " and BirthYear<=" + end_bd_date.ToString(): " 1=1 ";
                query += " and ";
                query += b_dd_date ? "DeceaseYear>=" + start_dd_date.ToString() + " and DeceaseYear<=" + end_dd_date.ToString() : " 1=1 ";
            }
            //MessageBox.Show(query);
            try
            {
                this._ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(query, this._conn);
                try { this._ds.Tables["FIND_ARTISTS"].Clear(); }
                catch (Exception ex) { };
                da.Fill(this._ds, "FIND_ARTISTS");
                table = this._ds.Tables["FIND_ARTISTS"];
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                //Разобраться почему ошибка
                return null;
            }
            return table;
        }


        /// <summary>
        /// Получение таблицы "Клиенты" 
        /// </summary>
        /// <returns></returns>
        public DataTable get_customers()
        {
            this._ds = new DataSet();
            this._da_customer = new SqlDataAdapter("select * from customer", this._conn);
            this._cb = new SqlCommandBuilder(this._da_customer);
            this._da_customer.Fill(this._ds, "CUSTOMER");
            this._active_table = "CUSTOMER";
            return _ds.Tables["CUSTOMER"];
        }

        /// <summary>
        /// Добавить клиента в базу данных 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="area_code"></param>
        /// <param name="local_number"></param>
        /// <param name="street"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zip_pc"></param>
        /// <param name="country"></param>
        /// <param name="email"></param>
        /// <returns>Возвращает OK если добавлен</returns>
        public string add_customer(string name, string area_code, string house_number,
                            string street, string city, string region,
                            string zip_pc, string country, string email, string ph_num)
        {
            this._command_text = "insert into CUSTOMER";
            this._command_text += "(Name, AreaCode, HouseNumber, Street, City, Region, ZipPostalCode, Country, Email, PhoneNumber) ";
            this._command_text += "values(";
            this._command_text += name == "" ? "null, " : "'" + name + "',";
            this._command_text += area_code == "" ? "null, " : "'" + area_code + "',";
            this._command_text += house_number == "" ? "null, " : "'" + house_number + "',";
            this._command_text += street == "" ? "null, " : "'" + street + "',";
            this._command_text += city == "" ? "null, " : "'" + city + "',";
            this._command_text += region == "" ? "null, " : "'" + region + "',";
            this._command_text += zip_pc == "" ? "null, " : "'" + zip_pc + "',";
            this._command_text += country == "" ? "null, " : "'" + country + "',";
            this._command_text += email == "" ? "null, " : "'" + email + "',";
            this._command_text += ph_num == "" ? "null, " : "'" + ph_num + "')";
            
            //MessageBox.Show(this._command_text);
            try
            {
                //MessageBox.Show(this._command_text);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = this._command_text;
                com.ExecuteNonQuery();
                com.Dispose();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
            }

            catch (Exception ex)
            {
                return (ex.Message + " " + ex.Data);
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            }
            return "OK";
        }

        public string get_customer_id_by_info(string name, string email)
        {
            string id;
            string query;

            query = "select CustomerID from CUSTOMER where ";
            query += "Name ";
            if (name == "") { query += "is null "; } 
            else    
                {
                    query += "= '" + name + "' ";
                }
            query += "and ";

            query += "Email ";
            if (email == "") { query += "is null "; } 
            else    
                {
                    query += "= '" + email + "' ";
                }
            
            
            //MessageBox.Show(query);

            try
            {
                //MessageBox.Show(this._command_text);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                id = null;
                while (rd.Read())
                {
                    id = rd.GetInt32(0).ToString();
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                return null;
            }

            return id;
        }

        public string get_work_id_by_info(string title, string copy)
        {
            string id;
            string query;

            query = "select WorkID from WORK where ";
            query += "Title ";
            if (title == "") { query += "is null "; }
            else
            {
                query += "= '";
                query += title;
                query += "' ";
            }
            query += "and ";

            query += "Copy ";
            if (copy == "") { query += "is null "; }
            else
            {
                query += "= '";
                query += copy;
                query += "' ";
            }


            //MessageBox.Show(query);

            try
            {
                //MessageBox.Show(this._command_text);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                id = null;
                while (rd.Read())
                {
                    id = rd.GetInt32(0).ToString();
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                return null;
            }

            return id;
        }

        public string upd_customer(int id, string name, string area_code, string house_number,
                            string street, string city, string region,
                            string zip_pc, string country, string email, string ph_num)
        {
            string query = "update CUSTOMER set ";

            query += "Name=";
            query += name == "" ? "null, " : "'" + name + "',";
            query += "AreaCode=";
            query += area_code == "" ? "null, " : "'" + area_code + "',";
            query += "HouseNumber=";
            query += house_number == "" ? "null, " : "'" + house_number + "',";
            query += "Street=";
            query += street == "" ? "null, " : "'" + street + "',";
            query += "City=";
            query += city == "" ? "null, " : "'" + city + "',";
            query += "Region=";
            query += region == "" ? "null, " : "'" + region + "',";
            query += "ZipPostalCode=";
            query += zip_pc == "" ? "null, " : "'" + zip_pc + "',";
            query += "Country=";
            query += country == "" ? "null, " : "'" + country + "',";
            query += "Email=";
            query += email == "" ? "null, " : "'" + email + "',";
            query += "PhoneNumber=";
            query += ph_num == "" ? "null, " : "'" + ph_num + "' ";
            query += "where CustomerID=" + id.ToString();

            //MessageBox.Show(query);
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
            }

            catch (Exception ex)
            {
                return ("ERROR_" + ex.Message + " " + ex.Data);
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            } 
            return "OK";
        }

        public string del_record_by_id(string table_name, int id)
        {
            string query = "";
            query += "delete from ";
            query += table_name;
            query += " where ";
            switch (table_name)
            {
                case "CUSTOMER": query += "CustomerID = ";    break;
                case "ARTIST":   query += "ArtistID = ";      break;
                case "WORK":     query += "WorkID = ";        break;
                case "TRANS":    query += "TransactionID = "; break;
                default:         return "ERROR_TABLE_NAME";
                    
            }
            query += id.ToString();
             
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
            }

            catch (Exception ex)
            {
                return ("ERROR" + " " + ex.Message + " " + ex.Data);
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            }

            return "OK";

        }

        public ArrayList get_interests_list(int id)
        {
            ArrayList artists = new ArrayList();
            string query = "select Name from ARTIST where ArtistID in (select ArtistID from CUSTOMER_ARTIST_INT where CustomerID=";
            query += id.ToString() + ")";
            //SqlConnection conn = new SqlConnection(this._connection_string);
            //conn.Open();
            SqlCommand com = this._conn.CreateCommand(); // new SqlCommand();
            com.CommandType = CommandType.Text;
            //com.Connection = conn;
            com.CommandText = query;
            try
            {
                SqlDataReader rd = com.ExecuteReader();
                while (rd.Read())
                {
                    artists.Add(rd.GetString(0));
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
                //conn.Dispose();
            }
            catch (Exception ex)
            {
                //error...
            }
            return artists;
        }

        
        public string add_artist(string name, string nat, string BirthYear, string dec_date)
        {
            string result;
            string query = "";
            query += "insert into ARTIST (Name, BirthYear, DeceaseYear, Nationality) values (";
            query += name == "" ? "null, " : "'" + name + "',";
            query += BirthYear == "" ? "null, " : "'" + BirthYear + "',";
            query += dec_date == "" ? "null, " : "'" + dec_date + "',";
            query += nat == "" ? "null, " : "'" + nat + "'";
            query += ")";
            
            SqlCommand com = this._conn.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = query;

            try
            {
                com.ExecuteNonQuery();
                com.Dispose();
                result = "OK";
            }

            catch (Exception ex)
            {
                result = "ERROR " + ex.Data + " " + ex.Message;
                return result;
            }
            return result;
        }

        public string upd_artist(int id, string name, string nat, string BirthYear, string dec_date)
        {
            string query = "update ARTIST set ";

            query += "Name=";
            query += name == "" ? "null, " : "'" + name + "',";
            query += "BirthYear=";
            query += BirthYear == "" ? "null, " : "'" + BirthYear + "',";
            query += "DeceaseYear=";
            query += dec_date == "" ? "null, " : "'" + dec_date + "',";
            query += "Nationality=";
            query += nat == "" ? "null, " : "'" + nat + "' ";
            query += "where ArtistID=" + id.ToString();

            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
            }

            catch (Exception ex)
            {
                return ("ERROR_" + ex.Message + " " + ex.Data);
            }

            return "OK";
        }

        public class_customer get_customer(int id)
        {
            class_customer customer = new class_customer();
            string query = "select CustomerID, Name, AreaCode, HouseNumber, Street, City, Region, ZipPostalCode, Country, Email, PhoneNumber from CUSTOMER where CustomerID=";
            query += id.ToString();
            //MessageBox.Show(query);
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    customer.result = "OK";
                    customer.customer_id = id.ToString();
                    customer.name = rd.GetString(1);
                    customer.area_code = rd.IsDBNull(2) ? "" : rd.GetString(2);
                    customer.local_number = rd.IsDBNull(3) ? "" : rd.GetString(3);
                    customer.street = rd.IsDBNull(4) ? "" : rd.GetString(4);
                    customer.city = rd.IsDBNull(5) ? "" : rd.GetString(5);
                    customer.state = rd.IsDBNull(6) ? "" : rd.GetString(6);
                    customer.zip_postal_code = rd.IsDBNull(7) ? "" : rd.GetString(7);
                    customer.country = rd.IsDBNull(8) ? "" : rd.GetString(8);
                    customer.email = rd.IsDBNull(9) ? "" : rd.GetString(9);
                    customer.phone_number = rd.IsDBNull(10) ? "" : rd.GetString(10);
                }
                rd.Close();
                rd.Dispose();
                com.Dispose(); //тогда здесь удалим
            }

            catch (Exception ex)
            {
                customer.result = "ERROR_" + ex.Data + " " + ex.Message;
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            }

            return customer;
        }

        public class_artist get_artist(int id)
        {
            class_artist artist = new class_artist();
            string query = "select ArtistID, Name, BirthYear, DeceaseYear, Nationality from ARTIST where ArtistID=";
            query += id.ToString();
            //MessageBox.Show(query);
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    artist.result = "OK";
                    artist.artist_id = id.ToString();
                    if (rd.IsDBNull(1))
                    {
                        artist.artist_name = "";
                    }
                    else
                    {
                        artist.artist_name = rd.GetString(1);
                    }

                    if (rd.IsDBNull(2))
                    {
                        artist.birthdate = "";
                    }
                    else 
                    {
                        artist.birthdate = rd.GetDecimal(2).ToString();
                    }

                    if (rd.IsDBNull(3))
                    {
                        artist.dc_date = "";
                    }
                    else
                    {
                        artist.dc_date = rd.GetDecimal(3).ToString();
                    }

                    if (rd.IsDBNull(4))
                    {
                        artist.nationality = "";
                    }
                    else
                    {
                        artist.nationality = rd.GetString(4);
                    }

                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                artist.result = "ERROR_" + ex.Data + " " + ex.Message;
            }
            return artist;
        }

        public string add_work(int artist_id, string title, string copy, string desc)
        {
            string result;
            string query = "";
            query += "insert into WORK (ArtistID, Title, Copy, Description) values (";

            if (artist_id.ToString() == "")
            { query += "null"; query += ", "; }
            else
            {
                query += artist_id;
                query += ", ";
            }

            if (title == "")
            { query += "null"; query += ", "; }
            else
            {
                query += " '";
                query += title;
                query += "',";
            }

            if (copy == "")
            { query += "null"; query += ", "; }
            else
            {
                query += " '";
                query += copy;
                query += "',";
            }

            if (desc == "")
            { query += "null"; query += ""; }
            else
            {
                query += " '";
                query += desc;
                query += "'";
            }
            query += ")";
            //MessageBox.Show(query);
            SqlCommand com = this._conn.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = query;
            //com.Dispose();

            try
            {
                com.ExecuteNonQuery();
                com.Dispose();
                result = "OK";
            }

            catch (Exception ex)
            {
                result = "ERROR " + ex.Data + " " + ex.Message;
                com.Dispose();
                return result;
            }
            return result;
        }

        public string upd_work(int work_id, int artist_id, string title, string copy, string desc)
        {
            string query = "update WORK set ";

            query += "ArtistID=";
            if (artist_id.ToString() == "") { query += "null, "; }
            else
            {
                query += "";
                query += artist_id.ToString();
                query += ", ";
            }

            query += "Title=";
            if (title == "") { query += "null, "; }
            else
            {
                query += "'";
                query += title;
                query += "', ";
            }

            query += "Copy=";
            if (copy == "") { query += "null, "; }
            else
            {
                query += "'";
                query += copy;
                query += "', ";
            }

            query += "Description=";
            if (desc == "") { query += "null "; }
            else
            {
                query += "'";
                query += desc;
                query += "' ";
            }

            query += "where WorkID=";
            query += work_id.ToString();

            //MessageBox.Show(query);

            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
                com.Dispose();
            }

            catch (Exception ex)
            {
                return ("ERROR_" + ex.Message + " " + ex.Data);
            }

            return "OK";
        }

        public class_work get_work(int id)
        {
            class_work work = new class_work();
            string query = "select WorkID, ArtistID, Title, Copy, Description from WORK where WorkID=";
            query += id.ToString();
            //MessageBox.Show(query);
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    work.result = "OK";
                    work.work_id = id.ToString();
                    if (rd.IsDBNull(1))
                    {
                        work.artist_id = "";
                    }
                    else
                    {
                        work.artist_id = rd.GetInt32(1).ToString();
                    }

                    if (rd.IsDBNull(2))
                    {
                        work.title = "";
                    }
                    else
                    {
                        work.title = rd.GetString(2);
                    }

                    if (rd.IsDBNull(3))
                    {
                        work.copy = "";
                    }
                    else
                    {
                        work.copy = rd.GetString(3);
                    }

                    if (rd.IsDBNull(4))
                    {
                        work.desc = "";
                    }
                    else
                    {
                        work.desc = rd.GetString(4);
                    }

                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                work.result = "ERROR_" + ex.Data + " " + ex.Message;
            }

            return work;

        }

        public ArrayList get_works_by_artist(int id)
        {
            ArrayList works = new ArrayList();
            string query = "select Title from WORK where ArtistID=";
            query += id.ToString();
            //MessageBox.Show(query);
            SqlCommand com = this._conn.CreateCommand();
            com.CommandText = query;
            try
            {
                SqlDataReader rd = com.ExecuteReader();
                while (rd.Read())
                {
                    works.Add(rd.GetString(0));
                }
                rd.Close();
                rd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                return null;
            }
            com.Dispose();
            return works;
        }

        public ArrayList get_list_by_table_and_column(string table_name, string column_name)
        {
            ArrayList list = new ArrayList();
            string query = "select distinct ";
            query += column_name;
            query += " from ";
            query += table_name;
            //MessageBox.Show(query);
            SqlCommand com = this._conn.CreateCommand();
            com.CommandText = query;
            try
            {
                SqlDataReader rd = com.ExecuteReader();
                while (rd.Read())
                {
                    if (!(rd.IsDBNull(0))) { list.Add(rd.GetString(0)); };
                }
                rd.Close();
                rd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                return list;
            }
            com.Dispose();
            return list;
        }

        public DataTable get_customers_table_by_param(bool b_name, bool b_city, bool b_country, bool b_trans,
                                            string name, string city, string country,
                                            int start_trans, int end_trans)
        {
            DataTable customers = new DataTable();
            string query = "";
            query += "select CustomerID, Name, Email, AreaCode, PhoneNumber, Street, City, Region, ZipPostalCode, Country, HouseNumber from CUSTOMER";
            if ((b_name) || (b_city) || (b_country) || (b_trans))
            {
                query += " where ";
                if (b_name)
                {
                    query += "Name like '%";
                    query += name;
                    query += "%'";
                }
                else
                {
                    query += " 1=1 ";
                }
                query += " and ";
                if (b_city)
                {
                    query += "City like '%";
                    query += city;
                    query += "%'";
                }
                else
                {
                    query += " 1=1 ";
                }
                query += " and ";
                if (b_country)
                {
                    query += "Country like '%";
                    query += city;
                    query += "%'";
                }
                else
                {
                    query += " 1=1 ";
                }
                query += " and ";
                if (b_trans)
                {
                   // select CustomerID from vrdb.dbo.TRANS 
                   // group by CustomerID
                   // having COUNT(CustomerID)>=1 and COUNT(CustomerID)<=3
                    query += "CustomerID in ";
                    query += "(select CustomerID from TRANS ";
                    query += "group by CustomerID ";
                    query += "having Count(CustomerID)>=";
                    query += start_trans.ToString();
                    query += " and Count(CustomerID)<=";
                    query += end_trans.ToString();
                    query += ")";
                }
                else
                {
                    query += " 1=1 ";
                }
                
            }
            //MessageBox.Show(query);
            try
            {

                this._ds = new DataSet();
                //this._da.Dispose();
                //this._cb.Dispose();
                //DataSet ds = new DataSet();
                //ds.Tables["CUSTOMER"].Clear();
                SqlDataAdapter da = new SqlDataAdapter(query, this._conn);
                //SqlCommandBuilder cb = new SqlCommandBuilder(da);
                try { this._ds.Tables["FIND_CUSTOMER"].Clear(); } catch (Exception ex) { };
                da.Fill(this._ds, "FIND_CUSTOMERS");
                customers = this._ds.Tables["FIND_CUSTOMERS"];
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                //Разобраться почему ошибка
                return null;
            }
            return customers;

        }


        public bool check_interest(int cust_id, int art_id)
        {
            string query = "select * from CUSTOMER_ARTIST_INT where CustomerID=";
            query += cust_id.ToString();
            query += " and ArtistID=";
            query += art_id.ToString();
            SqlCommand com = this._conn.CreateCommand();
            com.CommandText = query; 
            //MessageBox.Show(query);
            bool result = false;
            try
            {
                SqlDataReader rd = com.ExecuteReader();
                while (rd.Read())
                {
                    result = true;
                    break;
                }
                rd.Close();
                rd.Dispose();
                //return result;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при проверке налиичия художника\nв списке интересов клиента.\n" + ex.Data + " " + ex.Message);
            }
            com.Dispose();
            return result;
        }
        
        public void add_interest(int cust_id, int art_id)
        {
            string query = "";
            query += "insert into CUSTOMER_ARTIST_INT (CustomerID, ArtistID) values(";
            query += cust_id.ToString();
            query += ", ";
            query += art_id.ToString();
            query += ")";
            //MessageBox.Show(query);
            try
            {
                //SqlConnection conn = new SqlConnection(this._connection_string);
                //conn.Open();
                SqlCommand com = this._conn.CreateCommand(); // conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
                //conn.Close();
                //conn.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при добавлении интереса клиента!\n" + ex.Data + " " + ex.Message);
            }
        }

        public void del_interest(int cust_id, int art_id)
        {
            string query = "";
            query += "delete from CUSTOMER_ARTIST_INT where CustomerID=";
            query += cust_id.ToString();
            query += " and ArtistID=";
            query += art_id.ToString();
            //MessageBox.Show(query);
            try
            {
                //SqlConnection conn = new SqlConnection(this._connection_string);
                //conn.Open();
                SqlCommand com = this._conn.CreateCommand(); // conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
                //conn.Close();
                //conn.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show("Ошибка удаления интереса клиента!\n" + ex.Data + " " + ex.Message);
            }
        }

        public string get_work_state(int work_id)
        {
            string result = "NOT_IN_GALLERY"; // default -- not in gallery
            string query = "select TransactionID from TRANS where CustomerID is NULL and WorkID = " + work_id.ToString();
            try
            {
                //MessageBox.Show(query);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    { result = rd.GetInt32(0).ToString(); }
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Data + " " + ex.Message);
                return "ERROR";
                //Все путем -- результат выполнения будет веден в списке интересов клиента
                //MessageBox.Show(ex.Data + " " + ex.Message);
            }
            return result;
        }

        public class_transaction get_transaction(int trans_id)
        {
            class_transaction transaction = new class_transaction();
            string query = "select TRANS.TransactionID, TRANS.WorkID, WORK.Title, WORK.Copy, " +
                        "TRANS.CustomerID, CUSTOMER.Name, CUSTOMER.Email, TRANS.DateAcquired, " +
                        "TRANS.AcquisitionPrice, TRANS.PurchaseDate, TRANS.SalesPrice, TRANS.AskingPrice " +
                        "from TRANS " +
                        "left join WORK on TRANS.WorkID = WORK.WorkID " +
                        "left join CUSTOMER on TRANS.CustomerID = CUSTOMER.CustomerID " +
                        "where TRANS.TransactionID = " + trans_id.ToString();
            try
            {
                SqlConnection conn = new SqlConnection(this._connection_string);
                conn.Open();
                SqlCommand com = conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    transaction.result = "OK";
                    transaction.transaction_id = trans_id.ToString();
                    if (rd.IsDBNull(4))
                    {
                        transaction.customer_id = "";
                    }
                    else
                    {
                        transaction.customer_id = rd.GetInt32(4).ToString();
                    }
                    transaction.work_id = rd.GetInt32(1).ToString();
                    if (rd.IsDBNull(7))
                    {
                        transaction.data_acq = "";
                    }
                    else
                    {
                        transaction.data_acq = rd.GetDateTime(7).ToString();
                    }
                    if (rd.IsDBNull(5))
                    {
                        transaction.customer_name = "";
                    }
                    else
                    {
                        transaction.customer_name = rd.GetString(5).ToString();
                    }

                    if (rd.IsDBNull(6))
                    {
                        transaction.customer_email = "";
                    }
                    else
                    {
                        transaction.customer_email = rd.GetString(6).ToString();
                    }

                    if (rd.IsDBNull(2))
                    {
                        transaction.work_title = "";
                    }
                    else
                    {
                        transaction.work_title = rd.GetString(2).ToString();
                    }

                    if (rd.IsDBNull(3))
                    {
                        transaction.work_copy = "";
                    }
                    else
                    {
                        transaction.work_copy = rd.GetString(3).ToString();
                    }
                    if (rd.IsDBNull(8))
                    {
                        transaction.acq_price = "";
                    }
                    else
                    {
                        transaction.acq_price = rd.GetDecimal(8).ToString();
                    }
                    if (rd.IsDBNull(9))
                    {
                        transaction.pur_date = "";
                    }
                    else
                    {
                        transaction.pur_date = rd.GetDateTime(9).ToString();
                    }
                    if (rd.IsDBNull(10))
                    {
                        transaction.sales_price = "";
                    }
                    else
                    {
                        transaction.sales_price = rd.GetDecimal(10).ToString();
                    }
                    if (rd.IsDBNull(11))
                    {
                        transaction.ask_price = "";
                    }
                    else
                    {
                        transaction.ask_price = rd.GetDecimal(11).ToString();
                    }
                }
                rd.Close();
                rd.Dispose();
                com.Dispose(); 
            }

            catch (Exception ex)
            {
                transaction.result = "ERROR_" + ex.Data + " " + ex.Message;
            }
            return transaction;
        }

        public string create_transaction(int work_id, int cust_id, string date_acq, string acq_price, string pur_date,
                                            string sales_price, string ask_price)
        {
            this._command_text = "insert into TRANS";
            this._command_text += "(WorkID, CustomerID, DateAcquired, AcquisitionPrice, PurchaseDate, SalesPrice, AskingPrice) ";
            this._command_text += "values(";

            if (work_id.ToString() == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += "'";
                this._command_text += work_id.ToString();
                this._command_text += "',";
            }

            if (cust_id.ToString() == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += cust_id.ToString();
                this._command_text += "',";
            }

            if (date_acq == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += date_acq;
                this._command_text += "',";
            }

            if (acq_price == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += acq_price;
                this._command_text += "',";
            }

            if (pur_date == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += pur_date;
                this._command_text += "',";
            }

            if (sales_price == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += sales_price;
                this._command_text += "',";
            }

            if (ask_price == "")
            { this._command_text += "null"; this._command_text += ", "; }
            else
            {
                this._command_text += " '";
                this._command_text += ask_price;
                this._command_text += "')";
            }

            //MessageBox.Show(this._command_text);
            try
            {
                //MessageBox.Show(this._command_text);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = this._command_text;
                com.ExecuteNonQuery();
                com.Dispose();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
            }

            catch (Exception ex)
            {
                return (ex.Message + " " + ex.Data);
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            }
            return "OK";
        }
        
        public string upd_transaction(int trans_id, int work_id, string cust_id, string date_acq, string acq_price, string pur_date,
                                            string sales_price, string ask_price)
        {
            string query = "update TRANS set ";

            query += "WorkID=";
            if (work_id.ToString() == "") { query += "null, "; }
            else
            {
                query += "'";
                query += work_id.ToString();
                query += "', ";
            }

            query += "CustomerID=";
            if (cust_id == "") { query += "null, "; }
            else
            {
                query += "'";
                query += cust_id;
                query += "', ";
            }

            query += "DateAcquired=";
            if (date_acq == "") { query += "null, "; }
            else
            {
                query += "'";
                query += date_acq;
                query += "', ";
            }

            query += "AcquisitionPrice=";
            if (acq_price == "") { query += "null, "; }
            else
            {
                query += "";
                query += acq_price.Replace(",", ".");
                query += ", ";
            }

            query += "PurchaseDate=";
            if (pur_date == "") { query += "null, "; }
            else
            {
                query += "'";
                query += pur_date;
                query += "', ";
            }

            query += "SalesPrice=";
            if (sales_price == "") { query += "null, "; }
            else
            {
                query += "";
                query += sales_price.Replace(",", ".");
                query += ", ";
            }

            query += "AskingPrice=";
            if (ask_price == "") { query += "null "; }
            else
            {
                query += "";
                query += ask_price.Replace(",", ".");
                query += " ";
            }

            query += "where TransactionID=";
            query += trans_id.ToString();


            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
                //Обновить активную таблицу
                //this.apply_changes(this._active_table);
            }

            catch (Exception ex)
            {
                MessageBox.Show("Ошибка обновления транзакции!\nПроверьте коррекстность введенных данных");
                return ("ERROR_" + ex.Message + " " + ex.Data);
            }

            return "OK";
        }

        public string add_default_transaction(int work_id, string date_ack, string ack_price, string ask_price)
        {

            ack_price.Replace(",", ".");
            ask_price.Replace(",", ".");

            string result;
            string query = "";
            query += "insert into TRANS (WorkID, CustomerID, DateAcquired, AcquisitionPrice, PurchaseDate, SalesPrice, AskingPrice) values ("; //FIXME! (DataAcquired) - date?

            if (work_id.ToString() == "")
            { query += "null"; query += ", "; }
            else
            {
                query += work_id;
                query += ", ";
            }

            query += "";
            query += "null";
            query += ", ";
            

            if (date_ack == "")
            { query += "null"; query += ", "; }
            else
            {
                query += " '";
                query += date_ack;
                query += "',";
            }

            if (ack_price == "")
            { query += "null"; query += ", "; }
            else
            {
                query += " ";
                query += ack_price;
                query += ",";
            }


            query += " null, null, ";

            if (ask_price == "")
            { query += "null"; }
            else
            {
                query += " ";
                query += ask_price;
                query += "";
            }

            query += ")";

            MessageBox.Show(query);
            SqlCommand com = this._conn.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = query;
            com.Dispose();

            try
            {
                com.ExecuteNonQuery();
                com.Dispose();
                result = "OK";
            }

            catch (Exception ex)
            {
                result = "ERROR " + ex.Data + " " + ex.Message;
                return result;
            }
            return result;
        }

        public string get_last_identity()
        {
            string result = "NULL"; // default -- not in gallery
            string query = "select @@identity";
            try
            {
                //MessageBox.Show(query);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    if (rd.IsDBNull(0))
                    {
                        result = "NULL";
                    }
                    else
                    { result = rd.GetDecimal(0).ToString(); }
                }
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Data + " " + ex.Message);
                return "ERROR";
                //Все путем -- результат выполнения будет веден в списке интересов клиента
                //MessageBox.Show(ex.Data + " " + ex.Message);
            }
            return result;
        }

        public string execute_string_query(string query)
        {
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                com.ExecuteNonQuery();
                com.Dispose();
            }

            catch (Exception ex)
            {
                return "ERROR" + ex.Message + " " + ex.Data;
            }
            return "OK";
        }

        public string get_field_for_object(String table_name, String target_field, String post_field, String post_field_value)
        {
            string result = null;
            string query = "select " + target_field + " from " + table_name + " where " + post_field + " = '" + post_field_value + "'";
            try
            {
                //MessageBox.Show(query);
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                if (rd.Read())
                {
                    if (rd.IsDBNull(0))
                    {
                        result = null;
                    }
                    else
                    { result = rd.GetInt32(0).ToString(); }
                }
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Data + " " + ex.Message);
                return null;
            }
            return result;
        }
        
        public class_work_for_in_gallery_rep[] get_list_of_works_in_gallery()
        {
            class_work_for_in_gallery_rep[] works_array = new class_work_for_in_gallery_rep[128];
            works_array[1] = new class_work_for_in_gallery_rep();
            works_array[1].result = "EMPTY"; //empty default value

            string query =  "select WORK.WorkID, ARTIST.Name, Title, Copy, Description, AskingPrice from TRANS"
                        + " inner join WORK on TRANS.WorkID = WORK.WorkID"
                        + " inner join ARTIST on WORK.ArtistID = ARTIST.ArtistID"
                        + " where CustomerID is NULL";
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                int i = 0;
                while (rd.Read())
                {
                    i++;
                    works_array[i] = new class_work_for_in_gallery_rep();
                    works_array[i].result = "OK";
                    works_array[i].work_id = rd.GetInt32(0).ToString();
                    if (rd.IsDBNull(1))
                    {
                        works_array[i].artist_name = "";
                    }
                    else
                    {
                        works_array[i].artist_name = rd.GetString(1);
                    }

                    if (rd.IsDBNull(2))
                    {
                        works_array[i].work_title = "";
                    }
                    else
                    {
                        works_array[i].work_title = rd.GetString(2);
                    }

                    if (rd.IsDBNull(3))
                    {
                        works_array[i].work_copy = "";
                    }
                    else
                    {
                        works_array[i].work_copy = rd.GetString(3);
                    }

                    if (rd.IsDBNull(4))
                    {
                        works_array[i].work_desc = "";
                    }
                    else
                    {
                        works_array[i].work_desc = rd.GetString(4);
                    }

                    if (rd.IsDBNull(5))
                    {
                        works_array[i].trans_ask_price = "";
                    }
                    else
                    {
                        works_array[i].trans_ask_price = rd.GetDecimal(5).ToString();
                    }
                }
                //works_array[i].result = "LAST";
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                works_array[1].result = "ERROR_" + ex.Data + " " + ex.Message;
            }

            finally
            {
                //Удалить com (какого-то студия его не видит здесь)
            }

            return works_array;
        }

        public class_customer[] get_customers_list()
        {
            class_customer[] customers_list = new class_customer[512];
            string query = "select CustomerID, Name, Email from CUSTOMER";
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                int i = 0;
                while (rd.Read())
                {
                    i++;
                    customers_list[i] = new class_customer();
                    customers_list[i].result = "OK";
                    customers_list[i].customer_id = rd.GetInt32(0).ToString();
                    customers_list[i].name = rd.GetString(1).ToString();
                    customers_list[i].email = rd.GetString(2).ToString();
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " " + ex.Data);
                return null;
            }

            return customers_list;
        }

        public class_work[] get_works_list()
        {
            class_work[] works_list = new class_work[512];
            string query = "select WorkID, Title, Copy from WORK";
            try
            {
                SqlCommand com = this._conn.CreateCommand();
                com.CommandText = query;
                SqlDataReader rd = com.ExecuteReader();
                int i = 0;
                //MessageBox.Show("check...");
                while (rd.Read())
                {
                    i++;
                    works_list[i] = new class_work();
                    works_list[i].result = "OK";
                    works_list[i].work_id = rd.GetInt32(0).ToString();
                    works_list[i].title = rd.GetString(1).ToString();
                    works_list[i].copy = rd.GetString(2).ToString();
                }
                rd.Close();
                rd.Dispose();
                com.Dispose();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " " + ex.Data);
                return null;
            }

            return works_list;
        }


        /// <summary>
        /// Отразить (сохранить) изменения в GridView в таблице на сервере
        /// </summary>
        /// <param name="table_name">Имя таблицы</param>
        public void apply_changes(string table_name)
        {
            this._da.Update(this._ds, table_name);
        }

        public DataTable get_table_by_user_query(String sql_query)
        {
            DataTable table = null;
            try
            {

                this._ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sql_query, this._conn);
                try { this._ds.Tables["RESULTS_OF_USER_SQL_QUERY"].Clear(); }
                catch (Exception ex) { };
                da.Fill(this._ds, "RESULTS_OF_USER_SQL_QUERY");
                table = this._ds.Tables["RESULTS_OF_USER_SQL_QUERY"];
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR_" + ex.Data + " " + ex.Message);
                //Разобраться почему ошибка
                return null;
            }
            return table;
        }

        public DataSet get_purchase_dynamics()
        {
            string query = "select * from trans_dates left join purchase_dynamics on trans_dates.DateAcquired = purchase_dynamics.purDate left join acquired_dynamics on trans_dates.DateAcquired = acquired_dynamics.acqDate";
            //string query2 = "Select trans.DateAcquired, COUNT(Trans.DateAcquired) from TRANS where trans.CustomerID is null group by trans.DateAcquired order by trans.DateAcquired";
            //string query3 = "Select distinct DateAcquired from TRANS union Select distinct PurchaseDate from TRANS";
            try
            {
                this._ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(query, this._conn);
                try { this._ds.Tables["PURCHASE_DYNAMICS"].Clear(); }
                catch (Exception ex) { };
                da.Fill(this._ds, "PURCHASE_DYNAMICS");
                return this._ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Data + " " + ex.Message);
                return null;
            }
        }

    }
}
